﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FOModels.DepositSavingCurrency;
using System.Data;
namespace DataAccess.DepositSavingCurrency
{
    public class DepositSavingCurrencyDAL
    {
        private SQLDataAccess db;
        public DepositSavingCurrencyDAL()
        {
            db = new SQLDataAccess();
        }
        public DataTable getCurrencyCate(int langid)
        {
            return db.exeSelect("Select * from CurrencyCate cu join CurrencyCateLang cl on cu.CurrencyCateID = cl.CurrencyCateID where LangID = " + langid + "");
        }
        public DataTable getTOP1()
        {
            return db.exeSelect("select top 1 * from SavingCurrency order by SavingCurrencyID desc");
        }
        public DataTable getDepositSavingCurrencyByProviderDefault(int CompanyID,int langid)
        {
            return db.exeSelect("Select * from SavingCurrency sa join SavingCurrencyLang sl on sa.SavingCurrencyID = sl.SavingCurrencyID join  Company co on sa.CompanyID = co.CompanyID join CompanyLang coml on co.CompanyID = coml.CompanyID join TableTermInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.SavingDepositCategoryID = 1 and sa.MinimumBalance <= 1000 and sa.MaximumBalance >= 1000000 and  ta.[Month] = 12 and sa.Display = 1 and CurrencyCateID = 1 and coml.LangID = " + langid + " and sl.LangID = " + langid + " and sa.CompanyID = " + CompanyID + "");
        }
        public DataTable getDepositSavingCurrencyDefault(int langid)
        {
            return db.exeSelect("Select * from SavingCurrency sa join SavingCurrencyLang sl on sa.SavingCurrencyID = sl.SavingCurrencyID join  Company co on sa.CompanyID = co.CompanyID join CompanyLang coml on co.CompanyID = coml.CompanyID join TableTermInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.SavingDepositCategoryID = 1 and sa.MinimumBalance <= 1000 and sa.MaximumBalance >= 1000000 and  ta.[Month] = 12 and sa.Display = 1 and CurrencyCateID = 1 and coml.LangID = " + langid + " and sl.LangID = " + langid + "");
        }
        public DataTable getDepositSavingCurrencyAll(int cateid, int currencyid, int balance, int month, int langid)
        {
            return db.exeSelect("Select * from SavingCurrency sa join SavingCurrencyLang sl on sa.SavingCurrencyID = sl.SavingCurrencyID join  Company co on sa.CompanyID = co.CompanyID join CompanyLang coml on co.CompanyID = coml.CompanyID join TableTermInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.SavingDepositCategoryID = " + cateid + " and sa.MinimumBalance <= " + balance + " and sa.MaximumBalance >= " + balance + " and  ta.[Month] = " + month + " and sa.Display = 1 and CurrencyCateID =" + currencyid + " and coml.LangID = " + langid + " and sl.LangID = " + langid + "");
        }
        public DataTable getDepositSavingCurrencyByProvider(int cateid, int currencyid, int balance, int month, int CompanyID, int langid)
        {
            return db.exeSelect("Select * from SavingCurrency sa join SavingCurrencyLang sl on sa.SavingCurrencyID = sl.SavingCurrencyID join  Company co on sa.CompanyID = co.CompanyID join CompanyLang coml on co.CompanyID = coml.CompanyID join TableTermInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.SavingDepositCategoryID = " + cateid + " and sa.MinimumBalance <= " + balance + " and sa.MaximumBalance >= " + balance + " and  ta.[Month] = " + month + " and sa.Display = 1 and CurrencyCateID =" + currencyid + " and coml.LangID = " + langid + " and sl.LangID = " + langid + " and sa.CompanyID = " + CompanyID + "");
        }
        public DataTable getDepositSavingCurrencyByID(int SavingCurrencyID, int langid)
        {
            return db.exeSelect("select * from SavingCurrency ca join SavingCurrencyLang sl on ca.SavingCurrencyID = sl.SavingCurrencyID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.SavingCurrencyID = " + SavingCurrencyID + " and cl.LangID = " + langid + " and sl.LangID = " + langid + "");
        }
        public DataTable getDepositSavingCurrencyByIDManager(int SavingCurrencyID, int langid)
        {
            return db.exeSelect("select * from SavingCurrency ca join SavingCurrencyLang sl on ca.SavingCurrencyID = sl.SavingCurrencyID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where ca.SavingCurrencyID = " + SavingCurrencyID + " and cl.LangID = " + langid + " and sl.LangID = " + langid + "");
        }
        public DataTable getdepositsavingrelati(int CompanyID, int SavingCurrencyID, int langid)
        {
            return db.exeSelect("select * from SavingCurrency ca join SavingCurrencyLang sl on ca.SavingCurrencyID = sl.SavingCurrencyID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where ca.CompanyID = " + CompanyID + " and ca.SavingCurrencyID != " + SavingCurrencyID + " and cl.LangID = " + langid + " and sl.LangID = " + langid + "");
        }
        public DataTable getall(int langid)
        {
            return db.exeSelect("Select * from SavingCurrency sa join SavingCurrencyLang sl on sa.SavingCurrencyID = sl.SavingCurrencyID join Company co on sa.CompanyID = co.CompanyID join CurrencyCate cu on  sa.CurrencyCateID = cu.CurrencyCateID join CurrencyCateLang ccl on cu.CurrencyCateID = ccl.CurrencyCateID join CompanyLang cl on co.CompanyID = cl.CompanyID join Language la on sl.LangID = la.ID where sl.LangID = " + langid + " and cl.LangID = " + langid + " and ccl.LangID = " + langid + "");
        }
        public int UpdateDepositSavingCurrency(DepositSavingCurrencyModels models)
        {
            return db.exeUpdate("update SavingCurrency set SavingCurrencyName = N'" + models.SavingCurrencyName + "',MinimumBalance=" + double.Parse(models.MinimumBalance) + ",MaximumBalance=" + double.Parse(models.MaximumBalance) + ",SavingDepositCategoryID=" + models.SavingDepositCategoryID + ",CompanyID= " + models.CompanyID + ",CurrencyCateID=" + models.CurrencyCateID + ",Display='" + models.Display + "' where SavingCurrencyID = " + models.SavingCurrencyID + "");
        }
        public int InsertDepositSavingCurrency(DepositSavingCurrencyModels models)
        {
            return db.exeUpdate("insert into SavingCurrency values (N'" + models.SavingCurrencyName + "'," + double.Parse(models.MinimumBalance) + "," + double.Parse(models.MaximumBalance) + "," + models.CompanyID + "," + models.SavingDepositCategoryID + "," + models.CurrencyCateID + ",'" + models.Display + "')");
        }
        public int DeleteDepositSavingCurrency(int SavingCurrencyID)
        {
            return db.exeUpdate("Delete from SavingCurrency where SavingCurrencyID = " + SavingCurrencyID + "");
        }
       
        public int UpdateDepositSavingCurrencyLang(DepositSavingCurrencyModels models, int langid)
        {
            return db.exeUpdate("update SavingCurrencyLang set SavingCurrencyName = N'" + models.SavingCurrencyName + "',Description=N'" + models.Description + "',Benefit=N'" + models.Benefit + "',FeeContent=N'" + models.FeeContent + "',MoreInfo=N'" + models.MoreInfo + "' where SavingCurrencyID = " + models.SavingCurrencyID + " and LangID=" + langid + "");
        }
        public int InsertDepositSavingCurrencyLang(DepositSavingCurrencyModels models, int langid)
        {
            return db.exeUpdate("insert into SavingCurrencyLang values (" + models.SavingCurrencyID + "," + langid + ",N'" + models.SavingCurrencyName + "',N'" + models.Description + "',N'" + models.Benefit + "',N'" + models.FeeContent + "',N'" + models.MoreInfo + "')");
        }
        public int DeleteDepositSavingCurrencyLang(int SavingCurrencyID)
        {
            return db.exeUpdate("Delete from SavingCurrencyLang where SavingCurrencyID = " + SavingCurrencyID + "");
        }


        public int DeleteSavingCurrencyApply(int SavingCurrencyApplyID)
        {
            return db.exeUpdate("Delete from SavingCurrencyApply where SavingCurrencyApplyID = " + SavingCurrencyApplyID + "");
        }
        public int UpdateStatus(SavingCurrencyApplyModels card)
        {
            return db.exeUpdate("update SavingCurrencyApply set StatusID = " + card.StatusID + " where SavingCurrencyApplyID = " + card.SavingCurrencyApplyID + "");
        }
        public int SavingCurrencyApply(int SavingCurrencyID, SavingCurrencyApplyModels app)
        {
            return db.exeUpdate("insert into SavingCurrencyApply(SavingCurrencyID,CustomerName,CustomerEmail,CustomerPhone,CustomerIncome,CustomerDOB,StatusID) values (" + SavingCurrencyID + ",N'" + app.CustomerName + "',N'" + app.CustomerEmail + "',N'" + app.CustomerPhone + "',N'" + app.CustomerIncome + "',N'" + app.CustomerDOB + "'," + app.StatusID + ")");
        }
        public DataTable getcontractbyid(int cardapplyid)
        {
            return db.exeSelect("select * from SavingCurrencyApply ca join SavingCurrency c on ca.SavingCurrencyID = c.SavingCurrencyID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.SavingCurrencyApplyID = " + cardapplyid + " and cl.LangID = 1");
        }
        public DataTable getcontract()
        {
            return db.exeSelect("select * from SavingCurrencyApply ca join [SavingCurrency] c on ca.SavingCurrencyID = c.SavingCurrencyID join [Status] ta on ca.StatusID  = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = 1");
        }
        public DataTable getcontractbystatus(int statusid)
        {
            return db.exeSelect("select * from SavingCurrencyApply ca join [SavingCurrency] c on ca.SavingCurrencyID = c.SavingCurrencyID join [Status] ta on ca.StatusID  = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = 1 and ca.StatusID = " + statusid + "");
        }
    }
}
